-- @owner: opentestcase043
-- @date: 2022/5/18
-- @testpoint: 在关联条件，结合数组数据类型测试

--step1：创建表；expect：成功
drop table if exists t_ustore_related_cond_noequal_001_01 cascade;
create table t_ustore_related_cond_noequal_001_01(
col_1 bigint,
col_2 timestamp without time zone,
col_3 bool,
col_4 text,
col_5 char(30),
col_6 character varying(30),
col_7 blob,
col_8 integer,
col_9 interval day to second,
col_10 float8,
col_11 timestamp,
col_12 varchar2(50),
col_13 date,
col_14 decimal(12,6),
col_15 clob,
col_16 date,
col_17 int[],
col_18 integer,
col_19 int,
col_20 bigint,
constraint related_cond_noequal_pk_id1 primary key(col_1)
) with (storage_type=ustore);
drop table if exists t_ustore_related_cond_noequal_001_02 cascade;
create table t_ustore_related_cond_noequal_001_02(
col_1 bigint,
col_2 timestamp without time zone,
col_3 bool,
col_4 text,
col_5 char(30),
col_6 character varying(30),
col_7 blob,
col_8 integer,
col_9 interval day to second,
col_10 float8,
col_11 timestamp,
col_12 varchar2(50),
col_13 date,
col_14 decimal(12,6),
col_15 clob,
col_16 date,
col_17 int[],
col_18 integer,
col_19 int,
col_20 bigint,
constraint related_cond_noequal_pk_id2 primary key(col_1)
) with (storage_type=ustore);

--step2：创建序列；expect：成功
drop sequence if exists related_cond_noequal_001_seq_1;
create sequence related_cond_noequal_001_seq_1 increment by 1 start with 100000;
drop sequence if exists related_cond_noequal_001_seq_2;
create sequence related_cond_noequal_001_seq_2 increment by 1 start with 100000;

--step3：清空数据;；expect：成功
truncate table t_ustore_related_cond_noequal_001_01;
truncate table t_ustore_related_cond_noequal_001_02;

--step4：插入数据；expect：成功
begin
	for i in 1..60 loop
      insert into t_ustore_related_cond_noequal_001_01 values(
      related_cond_noequal_001_seq_1.nextval,
	  '2019-01-03 15:19:00',
	  true,
	  lpad('abc','10','a@123&^%djgk'),
	  lpad('abc','30','@')	  ,
	  lpad('abc','30','b'),
	  lpad('10','12','01010')::blob,
	  i,
	  (interval '4 5:12:10.222' day to second(3)),
	  i+445.255,
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  rpad('abc','30','e'),
	  '2019-01-03 15:19:00',
	  98*0.99,
	  rpad('abc','9','a@123&^%djgk'),
	  '2019-01-03 15:19:00',
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}',
	  related_cond_noequal_001_seq_1.nextval+10,
	  i+100,
	  related_cond_noequal_001_seq_1.nextval-100
	  );
    end loop;
end;
/
begin
	for i in 1..60 loop
      insert into t_ustore_related_cond_noequal_001_02 values(
      related_cond_noequal_001_seq_2.nextval,
	  '2019-01-03 15:19:00',
	  true,
	  lpad('abc','10','a@123&^%djgk'),
	  lpad('abc','30','@')	  ,
	  lpad('abc','30','b'),
	  lpad('10','12','01010')::blob,
	  i,
	  (interval '4 5:12:10.222' day to second(3)),
	  i+445.255,
	  to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
	  rpad('abc','30','e'),
	  '2019-01-03 15:19:00',
	  98*0.99,
	  rpad('abc','9','a@123&^%djgk'),
	  '2019-01-03 15:19:00',
	  '{32,535,5645645,6767,76,67,56,48,979,978,7}',
	  related_cond_noequal_001_seq_2.nextval+10,
	  i+100,
	  related_cond_noequal_001_seq_2.nextval-100
	  );
    end loop;
end;
/

--step5：在关联条件，结合数组数据类型测试；expect：成功
select t1.col_8,t2.col_17,t2.col_17[1]
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;
explain plan for select t1.col_8,t2.col_17,t2.col_17[1]
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;

select t2.col_17
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;
explain plan for select t2.col_17
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;

select t2.col_17[1]
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;
explain plan for select t2.col_17[1]
	from t_ustore_related_cond_noequal_001_01 t1
	join t_ustore_related_cond_noequal_001_02 t2
on t1.col_8>t2.col_17[1]+25 order by 1 limit 5;

--step6：清理环境；expect：成功
delete from plan_table;
drop sequence  related_cond_noequal_001_seq_1;
drop sequence  related_cond_noequal_001_seq_2;
drop table t_ustore_related_cond_noequal_001_01 cascade;
drop table t_ustore_related_cond_noequal_001_02 cascade;